package com.cx.healthex.client.admin.utils;

/**
 * JDBC工具类
 *
 * @author lennie
 */

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.sql.DataSource;

import com.alibaba.druid.pool.DruidDataSource;
import com.cx.base.apiModel.ApiModel;
import com.cx.base.utils.LogInfoUtil;

public class DBToolkit {
	
    //返回信息
    private static String logMsg;
    
    //连接数据库信息集
    public static List<Map<String, String>> list = new ArrayList<Map<String,String>>();
    
    //数据源集
    private static Map<String, DataSource> dataSources = new HashMap<String, DataSource>();
    
    //数据连接集
    private static Map<String, Connection> connections = new HashMap<String, Connection>();
    
    /**
     * 创建一个数据库连接
     *
     * @return 一个数据库连接
     */
    private static Connection getConnection(String templateId) throws Exception {
        Connection conn = connections.get(templateId);
        
        if(conn != null){
        	return conn;
        }
        
        //创建数据库连接
        try {
        	DataSource ds =  getDataSource(templateId);
            conn = ds.getConnection();
            connections.put(templateId, conn);
            //LogInfoUtil.debug(DBToolkit.class,null, "数据库连接："+conn.getMetaData().getUserName()+","+conn.getMetaData().getURL());
        } catch (IOException e) {
        	logMsg = "#ERROR# :failed to create connection!";
            LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
            throw new Exception(logMsg,e);
        } catch (SQLException e) {
        	logMsg = "#ERROR# :failed to create connection!";
            LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
            throw new Exception(logMsg,e);
		}
        return conn;
    }
    
    /**
     * 获取数据源
     * 
     * @param prefix
     * @return
     * @throws IOException
     * @throws SQLException
     */
	private synchronized static DataSource getDataSource(String templateId) throws IOException,SQLException {
		//到内存获取数据源
		DataSource ds = dataSources.get(templateId);
		if(ds != null){
			return ds;
		}
		
		//重新查询数据库，获取连接数据库信息并创建数据源存入缓存
		getIatRuleDataSource();
		//到内存获取数据源
		ds = dataSources.get(templateId);
		
		return ds;
	}
	
    /**
     * 获取所有数据源信息
     * 
     * @param prefix
     * @return
     * @throws IOException
     * @throws SQLException
     */
	private synchronized static void getIatRuleDataSource() throws IOException,SQLException {
		//获取规则库数据连接信息
		Properties props = new Properties();
		try {
			props.load(DBToolkit.class.getResourceAsStream("/common.properties"));
		} catch (IOException e) {
			LogInfoUtil.error(DBToolkit.class, "read-->>common.properties", "读取出错", e);
		}
		
		//查询数据库获取连接数据库信息
		DruidDataSource dataSourceTemp = createDataSource(props.getProperty("datasource.interact.driver"),props.getProperty("datasource.interact.url"),props.getProperty("datasource.interact.user"),props.getProperty("datasource.interact.password"));
		Connection conn = null;
		try {
			conn = dataSourceTemp.getConnection();
			String sql = "select * from iat_admin_dataSource";
			ArrayList<Map<String,String>> arrayList = queryForList(conn, sql,null);
			if(arrayList != null && arrayList.size() > 0){
				for(Map<String,String> m : arrayList){
					LogInfoUtil.info(DBToolkit.class, "dataSource-->"+m.get("template_id"), m.toString());
				}
				list = arrayList;
				
				//创建数据源
				for(Map<String, String> m : list){
					DruidDataSource dataSource = createDataSource(m.get("jdbc_driver"),m.get("jdbc_url"),m.get("jdbc_username"),m.get("jdbc_password"));
					dataSources.put(m.get("template_id"), dataSource);
				}
				
				LogInfoUtil.info(DBToolkit.class, "加载数据源", "successful");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 新建一个数据源
	 * */
	private static DruidDataSource createDataSource(String jdbcDriver,String jdbcUrl,String jdbcUsername,String jdbcPassword){
		try {
			DruidDataSource dataSource = new DruidDataSource();
			dataSource.setMaxActive(100);
			dataSource.setMaxWait(60000);
			dataSource.setMinIdle(20);
			dataSource.setInitialSize(10);
			dataSource.setPoolPreparedStatements(true);
			dataSource.setTestOnBorrow(false);
			dataSource.setTestOnReturn(false);
			dataSource.setMinEvictableIdleTimeMillis(300000);
			dataSource.setMaxWaitThreadCount(1000);
			dataSource.setFilters("stat");
			dataSource.setDriverClassName(jdbcDriver);
			dataSource.setUrl(jdbcUrl);  
			dataSource.setUsername(jdbcUsername);  
			dataSource.setPassword(jdbcPassword);  
			/*
			 * 临时设置 查看 isUseOracleImplicitCache 的调用
			 */
			dataSource.setUseOracleImplicitCache(false);
			
			return dataSource ;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
    /**
     * 定时访问数据库，保持连接有效
     */
    public static void keepConnection() throws Exception {
        try {
    		//遍历dataSources
			for (String key : connections.keySet()) {
				System.out.println("key= " + key + " and value= " + connections.get(key));
				String sql = "select 1  ";
				Statement stmt = connections.get(key).createStatement();
				stmt.execute(sql);
			}
        } catch (Exception e) {
            LogInfoUtil.error(DBToolkit.class, "keepConnection", "保持连接有效 ERROR", e);
        }
    }
	
    /**
     * 定时重新获取所有连接
     */
    public static void createALLConnection() throws Exception {
        try {
        	//重新查询数据库，获取连接数据库信息并创建数据源存入缓存
    		getIatRuleDataSource();
    		
    		Map<String, Connection> newConnections = new HashMap<String, Connection>();
    		Map<String, Connection> tempConnections = new HashMap<String, Connection>();
    		
    		//遍历dataSources
			for (String key : dataSources.keySet()) {
				System.out.println("key= " + key + " and value= " + dataSources.get(key));
				newConnections.put(key, dataSources.get(key).getConnection());
			}
			
			//换成新的连接
			tempConnections = connections;
			connections = newConnections;
			
			//关掉旧的连接
			for (String key : tempConnections.keySet()) {
				System.out.println("key= " + key + " and value= " + tempConnections.get(key));
				tempConnections.get(key).close();
			}

        } catch (Exception e) {
            LogInfoUtil.error(DBToolkit.class, "createALLConnection", "重新获取所有连接 ERROR", e);
        }
    }
    
	
    /**
     * 将某数据库中的查询结果转化成整数
     * @param prefix
     * @param sql
     * @return
     */
	public static Integer queryInteger(String templateId, String sql,List<Object> parameter) throws Exception{
		ResultSet rs = null;
		Connection conn = getConnection(templateId);
			try {
				if(conn!=null){
					rs = DBToolkit.executeQuery(conn, sql,parameter);
					while(rs.next()){
						return rs.getInt(1);
					}
				}
			} catch (Exception e) {
				LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
				throw new Exception(logMsg,e);
			}finally{
				try {
					rs.close();
				} catch (SQLException e) {
					LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
					throw new Exception(logMsg,e);
				}
			}
			return 0;
    }
    
    /**
     * 将某数据库中的查询结果转化成ArrayList<Map>
     * @param prefix
     * @param sql
     * @return
     */
    @SuppressWarnings("finally")
	public static ArrayList<Map<String, String>> queryForList(Connection conn, String sql,List<Object> parameter) throws Exception{
    	ArrayList<Map<String, String>> list = new ArrayList<Map<String, String>>();
		ResultSet rs = null;
			try {
				if(conn!=null){
					if(parameter == null){
						rs = DBToolkit.executeQuery(conn, sql);
					}else{
						rs = DBToolkit.executeQuery(conn, sql,parameter);
					}
					
					ResultSetMetaData md = rs.getMetaData();
					int columnCount = md.getColumnCount();
					while(rs.next()){
						Map<String, String> map = new HashMap<String, String>();
						for(int i=1;i<=columnCount;i++){
							String colName = md.getColumnName(i)+"";
							String colValue =  rs.getObject(i)+"";
							map.put(colName,colValue.replace("null", ""));
						}
						list.add(map);
					}
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
				throw new Exception(logMsg,e);
			}finally{
				try {
					rs.close();
				} catch (SQLException e) {
					LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
					throw new Exception(logMsg,e);
				}finally{
					return list;
				}
			}
    }
	

    /**
     * 在一个数据库连接上执行一个静态SQL语句查询
     *
     * @param conn      数据库连接
     * @param staticSql 静态SQL语句字符串
     * @return 返回查询结果集ResultSet对象
     */
    public static ResultSet executeQuery(Connection conn, String sql,List<Object> parameter) throws Exception {
        ResultSet rs = null;
        try {
            //创建执行SQL的对象
        	PreparedStatement stmt = (PreparedStatement) conn.prepareStatement(sql);
            
        	if(parameter != null && parameter.size() > 0){
        		for(int i = 0;i< parameter.size() ; i++){
        			 stmt.setObject(i+1, parameter.get(i));
        		}
        	}

            //执行SQL，并获取返回结果
            rs = stmt.executeQuery();
        } catch (SQLException e) {
            logMsg = "#ERROR# :failed to execute sql\n" + sql;
            LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
            throw new Exception(logMsg,e);
        }
        return rs;
    }
    
    /**
     * 在一个数据库连接上执行一个静态SQL语句查询
     *
     * @param conn      数据库连接
     * @param staticSql 静态SQL语句字符串
     * @return 返回查询结果集ResultSet对象
     */
    public static ResultSet executeQuery(Connection conn, String staticSql) throws Exception {
    	ResultSet rs = null;
        try {
            //创建执行SQL的对象
            Statement stmt = conn.createStatement();
            //执行SQL，并获取返回结果
            rs = stmt.executeQuery(staticSql);
        } catch (SQLException e) {
            logMsg = "#ERROR# :failed to execute sql\n" + staticSql;
            LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
            throw new Exception(logMsg,e);
        }
        return rs;
    }

    /**
     * 在一个数据库连接上执行一个静态SQL语句
     *
     * @param conn      数据库连接
     * @param staticSql 静态SQL语句字符串
     */
    public static void executeSQL(Connection conn, String staticSql) throws Exception {
        try {
            //创建执行SQL的对象
            Statement stmt = conn.createStatement();
            //执行SQL，并获取返回结果
            stmt.execute(staticSql);
        } catch (SQLException e) {
        	logMsg = "#ERROR# :failed to execute sql\n" + staticSql;
        	LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
            throw new Exception(logMsg,e);
        }
    }
    
    /**
     * 在一个数据库连接上执行一个SQL语句,并且提交事务
     *
     * @param conn      数据库连接
     * @param sql SQL语句字符串
     */
    public static void executeSQLWithCommit(Connection conn, String sql) throws Exception {
        try {
            //创建执行SQL的对象
            Statement stmt = conn.createStatement();
            //执行SQL，并获取返回结果
            stmt.execute(sql);
            conn.commit();
        } catch (SQLException e) {
        	logMsg = "#ERROR# :failed to execute sql\n" + sql;
        	LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
            throw new Exception(logMsg,e);
        }
    }
    
    /**
     * 在一个数据库连接上执行一个存储过程的命令,并且提交事务
     *
     * @param conn      数据库连接
     * @param sql 调用存储过程的命令
     */
    @SuppressWarnings("rawtypes")
	public static ApiModel callProcWithCommit(Connection conn, String sql, Object...inVars) throws Exception {
    	ApiModel apiModel = new ApiModel();
    	try {
            //创建执行SQL的对象
        	CallableStatement stmt = conn.prepareCall(sql);
        	stmt.registerOutParameter(1, Types.VARCHAR);
        	stmt.registerOutParameter(2, Types.VARCHAR);
        	for(int i=0;i<inVars.length;i++){
        		Object obj = inVars[i];
        		int typeId = Types.VARCHAR;
        		if(obj instanceof Integer){
        			typeId = Types.INTEGER;
        		} else if(obj instanceof Date){
        			typeId = Types.DATE;
        		} else if(obj instanceof Float){
        			typeId = Types.FLOAT;
        		} else if(obj instanceof Long) {
        			typeId = Types.BIGINT;
        		}
        		stmt.setObject(i+3, obj, typeId);
        	}
            //执行SQL，并获取返回结果
            stmt.execute();
            
            apiModel.setStatus(stmt.getString(1));
            apiModel.setErrorMsg(stmt.getString(2));
            conn.commit();
            
        } catch (SQLException e) {
        	logMsg = "#ERROR# :failed to execute sql\n" + sql;
        	LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
            throw new Exception(logMsg,e);
        }
    	return apiModel;
    }

    /**
     * 在一个数据库连接上执行一批静态SQL语句
     *
     * @param conn    数据库连接
     * @param sqlList 静态SQL语句字符串集合
     */
    public static void executeBatchSQL(Connection conn, List<String> sqlList) throws Exception {
        try {
            //创建执行SQL的对象
            Statement stmt = conn.createStatement();
            for (String sql : sqlList) {
                stmt.addBatch(sql);
            }
            //执行SQL，并获取返回结果
            stmt.executeBatch();
        } catch (SQLException e) {
            LogInfoUtil.error(DBToolkit.class, null, "#ERROR# :failed to batch execute sql", e);
        }
    }

    public static void closeConnection(Connection conn) throws Exception{
        if (conn == null) return;
        try {
            if (!conn.isClosed()) {
                //关闭数据库连接
                conn.close();
            }
        } catch (SQLException e) {
            logMsg = "#ERROR# :failed to close connection";
            LogInfoUtil.error(DBToolkit.class, null, logMsg, e);
            throw new Exception(logMsg,e);
        }
    }
    

}